Re: ISOLATION LEVEL SERIALIZABLE

Wow. RTFM for me huh? Thanks for the heads up folks!

>power2themacs <power2themacs(at)yahoo(dot)com> writes:
>
>> >In table two you would not create a serial instead you would create an
>> >INTEGER because serial is a counter and the values in table 2 may not be
>> >in table 1.
>> >
>> >Use a transaction like as follows
>> >
>> >BEGIN;
>> >INSERT INTO TABLE1 VALUES (Whatever values);
>> >var = SELECT CURRVAL('sequence_name');
>> >INSERT INTO TABLE2 VALUES (var,whatever else);
>> >COMMIT;
>> >
>>
>> But this is the race condition I am trying to avoid. Someone can
>> insert before I get the currval and it will beincremented and this
>> will result in invalid data. Right now, I'm doing exactly that but I
>> add SET ISOLATION LEVEL SERIALIZABLE; after the transaction which
>> locks out other INSERTS. I think I'll try PG Explorer's idea. Thanks
>> PG Explorer!
>
>Actually currval is precisely what you need. It will return the
>current value of the sequence in question for your particular backend
>without paying attention to what might be going on in another
>connection. So the above transaction is perfectly safe, and is, in
>fact, the standard way of writing these sorts of transactions in
>PostgreSQL.
>
>So you can rest assured that I am not making this up, here's the
>relevant bit from the PostgreSQL documentation.
>
> currval
>
> Return the value most recently obtained by nextval for this
> sequence in the current server process. (An error is reported
> if nextval has never been called for this sequence in this
> process.) Notice that because this is returning a
> process-local value, it gives a predictable answer even if
> other server processes are executing nextval meanwhile.
>
>I hope this is helpful,
>
>Jason

--
><><><><><><><><><><><><
AgentM
agentm(at)cmu(dot)edu

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com